{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Purchasing tickets\n",
    "## An example use case of the Digital Twin\n",
    "\n",
    "This example just includes:\n",
    "* Connecting\n",
    "* Building relationships between models\n",
    "* Updating values\n",
    "* Modifying the relationships and updating twins\n",
    "* Querying twins by relationship\n",
    "\n",
    "In the previous scenario we made a bunch of customers. In this scenario we built a bunch of individual users \n",
    "[This is the SDK repo on Github](https://github.com/Azure/azure-sdk-for-python/tree/4559e19e2f3146a49f1eba1706bb798071f4a1f5/sdk/digitaltwins/azure-digitaltwins-core)\n",
    "\n",
    "[Here is the doc on the query language](https://docs.microsoft.com/en-us/azure/digital-twins/concepts-query-language)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<azure.digitaltwins.core._digitaltwins_client.DigitalTwinsClient at 0x2581c5a78c8>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from azure.identity import AzureCliCredential\n",
    "from azure.digitaltwins.core import DigitalTwinsClient\n",
    "\n",
    "# using yaml instead of \n",
    "import yaml\n",
    "import uuid\n",
    "\n",
    "# using altair instead of matplotlib for vizuals\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# you will get this from the ADT resource at portal.azure.com\n",
    "your_digital_twin_url = \"home-test-twin.api.wcus.digitaltwins.azure.net\"\n",
    "\n",
    "azure_cli = AzureCliCredential()\n",
    "service_client = DigitalTwinsClient(\n",
    "    your_digital_twin_url, azure_cli)\n",
    "service_client"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So from the previous notebook we uploaded some models. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'display_name': {'en': 'Patron'},\n",
       " 'description': {'en': 'As an example, contains all of the properties possible in the DTDL.'},\n",
       " 'id': 'dtmi:billmanh:patron;1',\n",
       " 'upload_time': '2020-11-30T00:26:34.514069Z',\n",
       " 'decommissioned': False}"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patron = service_client.get_model(\"dtmi:mymodels:patron;1\")\n",
    "patron.as_dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And we have several `customers` in our ecosystem that are made from the `patron` model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "query_expression = \"SELECT * FROM digitaltwins t where IS_OF_MODEL('dtmi:billmanh:patron;1')\"\n",
    "query_result = service_client.query_twins(query_expression)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note** the query object loves to drop values. To keep from making multiple queries, save the data somewhere. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "values = []\n",
    "for i in query_result:\n",
    "    values.append(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customers = pd.DataFrame([[i['$dtId'],i['satisfaction']] for i in values],\n",
    "                           columns=['id','satisfaction'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>satisfaction</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>customer-cc04f3b6-39b0-4cef-bfff-a7d668cce446</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>customer-3cbd5e60-957d-44ff-944f-9adb42a20a52</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>customer-5c454e2f-f70b-4352-b75a-958f1a49beba</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>customer-26196fee-5ffd-457a-86b7-192a998f3cf2</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>customer-e6f49d8a-711b-41c3-9db8-c7ece3dbc32c</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>customer-45e9aa03-733d-4a99-b9d5-94f1c6b04214</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>customer-0234cb48-1fa2-43e0-b69d-36a6ff269666</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>customer-75b2f757-faee-4a85-bc93-e6e9ff7cd891</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>customer-048f85a8-173e-4305-92b8-ead2a748b07f</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>customer-25e19268-3433-4f09-afe3-94f466313368</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               id  satisfaction\n",
       "0   customer-cc04f3b6-39b0-4cef-bfff-a7d668cce446            10\n",
       "1   customer-3cbd5e60-957d-44ff-944f-9adb42a20a52            10\n",
       "2   customer-5c454e2f-f70b-4352-b75a-958f1a49beba             7\n",
       "3   customer-26196fee-5ffd-457a-86b7-192a998f3cf2             9\n",
       "4   customer-e6f49d8a-711b-41c3-9db8-c7ece3dbc32c             7\n",
       "..                                            ...           ...\n",
       "79  customer-45e9aa03-733d-4a99-b9d5-94f1c6b04214             9\n",
       "80  customer-0234cb48-1fa2-43e0-b69d-36a6ff269666             9\n",
       "81  customer-75b2f757-faee-4a85-bc93-e6e9ff7cd891             6\n",
       "82  customer-048f85a8-173e-4305-92b8-ead2a748b07f             8\n",
       "83  customer-25e19268-3433-4f09-afe3-94f466313368            10\n",
       "\n",
       "[84 rows x 2 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's add another model for `tickets`.  In our very simple model, we will assume that `customers` will buy `tickets` to our events."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# service_client.delete_model(ticket_model_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ticket_model_json = yaml.safe_load(open(\"models/ticket.json\"))\n",
    "# service_client.create_models([ticket_model_json])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'display_name': {'en': 'ticket'},\n",
       " 'description': {'en': 'an abstract ticket'},\n",
       " 'id': 'dtmi:billmanh:ticket;1',\n",
       " 'upload_time': '2020-11-26T20:12:34.578813Z',\n",
       " 'decommissioned': False}"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ticket_model_id = \"dtmi:mymodels:ticket;1\"\n",
    "get_model = service_client.get_model(ticket_model_id)\n",
    "get_model.as_dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the dict returned from the service doesn't contain all of the values that you created, but that's ok. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_twin(name,model_id):\n",
    "    digital_twin_id = f'{name}-{str(uuid.uuid4())}'\n",
    "    dt_json = {\n",
    "        \"$metadata\": {\n",
    "            \"$model\": model_id\n",
    "        }\n",
    "    }\n",
    "    return digital_twin_id,dt_json\n",
    "\n",
    "def updsert_twin():\n",
    "    created_twin = service_client.upsert_digital_twin(digital_twin_id, dt_json)\n",
    "    return created_twin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "ticket_id, ticket_json = generate_twin(\"ticket\",ticket_model_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'$metadata': {'$model': 'dtmi:billmanh:ticket;1'}}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ticket_json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So when I create the `tickets` I’m going to create them as unsold (`available`) and for a range of events. I'm just going to introduce some tickets into the system for some shows coming up. These tickets just exist locally at this point, but we will 'go live' when we push them into the ecosystem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>$metadata</th>\n",
       "      <th>event_title</th>\n",
       "      <th>state</th>\n",
       "      <th>ticket_location</th>\n",
       "      <th>uid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "      <td>0</td>\n",
       "      <td>ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "      <td>1</td>\n",
       "      <td>ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "      <td>2</td>\n",
       "      <td>ticket-260e3349-adc5-41af-9ae5-4f3690d813c7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "      <td>3</td>\n",
       "      <td>ticket-9f2bc5f4-b7d6-4f86-a1f8-c570cb263717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "      <td>4</td>\n",
       "      <td>ticket-cf70a216-0183-4458-8e13-94f432f7b8c0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "      <td>0</td>\n",
       "      <td>ticket-57a45e6f-f42a-41b0-a0f5-68e5e0fd67ba</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "      <td>1</td>\n",
       "      <td>ticket-7f414b3f-fde6-4cbc-ad58-86c7ccfb1109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "      <td>2</td>\n",
       "      <td>ticket-2d69b397-3ec3-4588-9eaf-5eb931f1f8c4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "      <td>3</td>\n",
       "      <td>ticket-e9fc6ad3-d7ad-4ef1-8a58-0759a0f27a00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "      <td>4</td>\n",
       "      <td>ticket-dc48bc69-b1f5-4850-b5af-037c4ea72961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "      <td>0</td>\n",
       "      <td>ticket-b7a550e8-0fa8-46ff-8b4c-1a290a5d706c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "      <td>1</td>\n",
       "      <td>ticket-92826a40-b024-4b91-a4d2-7e20dcfddce3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "      <td>2</td>\n",
       "      <td>ticket-8c483b5c-37d4-4027-8d67-d920bf8ca063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "      <td>3</td>\n",
       "      <td>ticket-09028447-8e23-4e4f-a100-098a4dc8e919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1'}</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "      <td>4</td>\n",
       "      <td>ticket-ccc89397-0d52-42a3-8fe1-39c97158c87c</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               $metadata        event_title state  \\\n",
       "0   {'$model': 'dtmi:billmanh:ticket;1'}            Nirvana  open   \n",
       "1   {'$model': 'dtmi:billmanh:ticket;1'}            Nirvana  open   \n",
       "2   {'$model': 'dtmi:billmanh:ticket;1'}            Nirvana  open   \n",
       "3   {'$model': 'dtmi:billmanh:ticket;1'}            Nirvana  open   \n",
       "4   {'$model': 'dtmi:billmanh:ticket;1'}            Nirvana  open   \n",
       "5   {'$model': 'dtmi:billmanh:ticket;1'}  Smashing Pumpkins  open   \n",
       "6   {'$model': 'dtmi:billmanh:ticket;1'}  Smashing Pumpkins  open   \n",
       "7   {'$model': 'dtmi:billmanh:ticket;1'}  Smashing Pumpkins  open   \n",
       "8   {'$model': 'dtmi:billmanh:ticket;1'}  Smashing Pumpkins  open   \n",
       "9   {'$model': 'dtmi:billmanh:ticket;1'}  Smashing Pumpkins  open   \n",
       "10  {'$model': 'dtmi:billmanh:ticket;1'}       Foo Fighters  open   \n",
       "11  {'$model': 'dtmi:billmanh:ticket;1'}       Foo Fighters  open   \n",
       "12  {'$model': 'dtmi:billmanh:ticket;1'}       Foo Fighters  open   \n",
       "13  {'$model': 'dtmi:billmanh:ticket;1'}       Foo Fighters  open   \n",
       "14  {'$model': 'dtmi:billmanh:ticket;1'}       Foo Fighters  open   \n",
       "\n",
       "    ticket_location                                          uid  \n",
       "0                 0  ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbe  \n",
       "1                 1  ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9  \n",
       "2                 2  ticket-260e3349-adc5-41af-9ae5-4f3690d813c7  \n",
       "3                 3  ticket-9f2bc5f4-b7d6-4f86-a1f8-c570cb263717  \n",
       "4                 4  ticket-cf70a216-0183-4458-8e13-94f432f7b8c0  \n",
       "5                 0  ticket-57a45e6f-f42a-41b0-a0f5-68e5e0fd67ba  \n",
       "6                 1  ticket-7f414b3f-fde6-4cbc-ad58-86c7ccfb1109  \n",
       "7                 2  ticket-2d69b397-3ec3-4588-9eaf-5eb931f1f8c4  \n",
       "8                 3  ticket-e9fc6ad3-d7ad-4ef1-8a58-0759a0f27a00  \n",
       "9                 4  ticket-dc48bc69-b1f5-4850-b5af-037c4ea72961  \n",
       "10                0  ticket-b7a550e8-0fa8-46ff-8b4c-1a290a5d706c  \n",
       "11                1  ticket-92826a40-b024-4b91-a4d2-7e20dcfddce3  \n",
       "12                2  ticket-8c483b5c-37d4-4027-8d67-d920bf8ca063  \n",
       "13                3  ticket-09028447-8e23-4e4f-a100-098a4dc8e919  \n",
       "14                4  ticket-ccc89397-0d52-42a3-8fe1-39c97158c87c  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def generate_tickets(title,n_tickets):\n",
    "    tickets = []\n",
    "    for i in range(n_tickets):\n",
    "        ticket_id, ticket_json = generate_twin(\"ticket\",ticket_model_id)\n",
    "        ticket_json['event_title'] = title\n",
    "        ticket_json['state'] = 'open'\n",
    "        ticket_json['ticket_location'] = i\n",
    "        ticket_json['uid'] = f'ticket-{str(uuid.uuid4())}'\n",
    "        tickets.append(ticket_json)\n",
    "    return tickets\n",
    "\n",
    "tickets_df = pd.concat([pd.DataFrame(generate_tickets('Nirvana',5)),\n",
    "          pd.DataFrame(generate_tickets('Smashing Pumpkins',5)),\n",
    "           pd.DataFrame(generate_tickets('Foo Fighters',5))]).reset_index(drop=True)\n",
    "\n",
    "tickets_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok now that we know what tickets we want to sell, let's push them to the digital twin ecosystem. This is exactly the same as what we did with `Customers` in step one. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "ticket_twins = []\n",
    "for i in tickets_df.index:\n",
    "    ticket_data = tickets_df.loc[i]\n",
    "    ticket_twin_id = ticket_data['uid']\n",
    "    ticket_json = ticket_data.drop('uid').to_dict()\n",
    "    created_twin = service_client.upsert_digital_twin(ticket_twin_id, ticket_json)\n",
    "    ticket_twins.append(created_twin)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'$dtId': 'ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbe',\n",
       "  '$etag': 'W/\"172ddd8b-a58c-41da-9081-19b0ff611ffa\"',\n",
       "  'event_title': 'Nirvana',\n",
       "  'state': 'open',\n",
       "  'ticket_location': '0',\n",
       "  '$metadata': {'$model': 'dtmi:billmanh:ticket;1',\n",
       "   'event_title': {'lastUpdateTime': '2020-12-07T01:30:50.2153563Z'},\n",
       "   'state': {'lastUpdateTime': '2020-12-07T01:30:50.2153563Z'},\n",
       "   'ticket_location': {'lastUpdateTime': '2020-12-07T01:30:50.2153563Z'}}},\n",
       " {'$dtId': 'ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9',\n",
       "  '$etag': 'W/\"f775c7be-176a-401a-b427-bd7797752283\"',\n",
       "  'event_title': 'Nirvana',\n",
       "  'state': 'open',\n",
       "  'ticket_location': '1',\n",
       "  '$metadata': {'$model': 'dtmi:billmanh:ticket;1',\n",
       "   'event_title': {'lastUpdateTime': '2020-12-07T01:30:50.8441094Z'},\n",
       "   'state': {'lastUpdateTime': '2020-12-07T01:30:50.8441094Z'},\n",
       "   'ticket_location': {'lastUpdateTime': '2020-12-07T01:30:50.8441094Z'}}},\n",
       " {'$dtId': 'ticket-260e3349-adc5-41af-9ae5-4f3690d813c7',\n",
       "  '$etag': 'W/\"8670c70a-b849-4d0c-abbc-5bcf6b77ec21\"',\n",
       "  'event_title': 'Nirvana',\n",
       "  'state': 'open',\n",
       "  'ticket_location': '2',\n",
       "  '$metadata': {'$model': 'dtmi:billmanh:ticket;1',\n",
       "   'event_title': {'lastUpdateTime': '2020-12-07T01:30:51.0152850Z'},\n",
       "   'state': {'lastUpdateTime': '2020-12-07T01:30:51.0152850Z'},\n",
       "   'ticket_location': {'lastUpdateTime': '2020-12-07T01:30:51.0152850Z'}}}]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ticket_twins[:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Delete twins if you want\n",
    "# for i in tickets_df.index:\n",
    "#     ticket_data = tickets_df.loc[i]\n",
    "#     ticket_twin_id = ticket_data['uid']\n",
    "#     service_client.delete_digital_twin(ticket_twin_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So now I have `customers` and I have `tickets`, but there is no relationship between them. Let's pretend that I have a website that sells tickets. When a `customer` buys a `ticket` on the website, a relationship between the two is established AND the status of the ticket changes to sold. \n",
    "\n",
    "* website (or mobile app) will tell the user which tickets are still available, and have features that allow them to search by row, or by concert.\n",
    "* the website (or mobile app) gets the latitude and longitude of the user when they buy the tickets. \n",
    "* once purchased, the status of that ticket changes to `sold`.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The user experience (mobile app)\n",
    "\n",
    "Here is where the user will buy tickets. \n",
    "\n",
    "This cell is a customer experience. The `customer` enters the website and looks up the available tickets for the show they want. This data comes from the website. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# selection from a dropdown menu or something\n",
    "selection_show = 'Nirvana'\n",
    "# just grabbing the first customer. \n",
    "user = df_customers.loc[0]['id']\n",
    "user_lat = np.random.randint(0,100)\n",
    "user_long = np.random.randint(0,100)\n",
    "\n",
    "query_expression = f\"\"\"\n",
    "SELECT * FROM digitaltwins where IS_OF_MODEL('{ticket_model_id}') \n",
    "and state = 'open'\n",
    "and event_title = '{selection_show}'\n",
    "\"\"\"\n",
    "query_result = service_client.query_twins(query_expression)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the client dumps records after the query. Try running the cell below a few times. If you need to use the values again and again you need to put them into memory somewhere. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>$dtId</th>\n",
       "      <th>event_title</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbe</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ticket-cf70a216-0183-4458-8e13-94f432f7b8c0</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ticket-260e3349-adc5-41af-9ae5-4f3690d813c7</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ticket-9f2bc5f4-b7d6-4f86-a1f8-c570cb263717</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         $dtId event_title state\n",
       "0  ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbe     Nirvana  open\n",
       "1  ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9     Nirvana  open\n",
       "2  ticket-cf70a216-0183-4458-8e13-94f432f7b8c0     Nirvana  open\n",
       "3  ticket-260e3349-adc5-41af-9ae5-4f3690d813c7     Nirvana  open\n",
       "4  ticket-9f2bc5f4-b7d6-4f86-a1f8-c570cb263717     Nirvana  open"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "available_tickets_df = pd.DataFrame([[i['$dtId'],i['event_title'],i['state']] for i in query_result],\n",
    "                                   columns = ['$dtId','event_title','state'])\n",
    "available_tickets_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now the user chooses to buy a ticket. The ticket state changes to closed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'$dtId': 'ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbe',\n",
       " '$etag': 'W/\"172ddd8b-a58c-41da-9081-19b0ff611ffa\"',\n",
       " 'event_title': 'Nirvana',\n",
       " 'state': 'open',\n",
       " 'ticket_location': '0',\n",
       " '$metadata': {'$model': 'dtmi:billmanh:ticket;1',\n",
       "  'event_title': {'lastUpdateTime': '2020-12-07T01:30:50.2153563Z'},\n",
       "  'state': {'lastUpdateTime': '2020-12-07T01:30:50.2153563Z'},\n",
       "  'ticket_location': {'lastUpdateTime': '2020-12-07T01:30:50.2153563Z'}}}"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customer_selection = available_tickets_df.loc[0]['$dtId']\n",
    "\n",
    "service_client.get_digital_twin(customer_selection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "customer_selection = available_tickets_df.loc[0]['$dtId']\n",
    "\n",
    "patch = [\n",
    "    {\n",
    "        \"op\": \"replace\",\n",
    "        \"path\": \"\",\n",
    "        \"value\": \"sold\"\n",
    "    }\n",
    "]\n",
    "service_client.update_component(customer_selection,\"state\", patch)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**NOTE** that the path is `\"\"` when the item is at the root of the state."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>$dtId</th>\n",
       "      <th>event_title</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ticket-600f2149-1ed8-4cb4-b0b8-bb6d03f575cd</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ticket-b8860b9b-cc54-4591-abb3-2abc3adc0800</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ticket-2256b8c9-1dd8-4ef8-a00a-b191dc122532</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ticket-99023c6e-5d97-4e43-ab08-3d893233c8b0</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ticket-132839a8-a4a3-4c75-ae96-9ff4085a07d4</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbe</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ticket-cf70a216-0183-4458-8e13-94f432f7b8c0</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ticket-260e3349-adc5-41af-9ae5-4f3690d813c7</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ticket-9f2bc5f4-b7d6-4f86-a1f8-c570cb263717</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         $dtId event_title state\n",
       "0  ticket-600f2149-1ed8-4cb4-b0b8-bb6d03f575cd     Nirvana  sold\n",
       "1  ticket-b8860b9b-cc54-4591-abb3-2abc3adc0800     Nirvana  sold\n",
       "2  ticket-2256b8c9-1dd8-4ef8-a00a-b191dc122532     Nirvana  sold\n",
       "3  ticket-99023c6e-5d97-4e43-ab08-3d893233c8b0     Nirvana  sold\n",
       "4  ticket-132839a8-a4a3-4c75-ae96-9ff4085a07d4     Nirvana  sold\n",
       "5  ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbe     Nirvana  open\n",
       "6  ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9     Nirvana  open\n",
       "7  ticket-cf70a216-0183-4458-8e13-94f432f7b8c0     Nirvana  open\n",
       "8  ticket-260e3349-adc5-41af-9ae5-4f3690d813c7     Nirvana  open\n",
       "9  ticket-9f2bc5f4-b7d6-4f86-a1f8-c570cb263717     Nirvana  open"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_expression = f\"\"\"\n",
    "SELECT * FROM digitaltwins where IS_OF_MODEL('{ticket_model_id}') \n",
    "and event_title = '{selection_show}'\n",
    "\"\"\"\n",
    "query_result = service_client.query_twins(query_expression)\n",
    "available_tickets_df = pd.DataFrame([[i['$dtId'],i['event_title'],i['state']] for i in query_result],\n",
    "                                   columns = ['$dtId','event_title','state'])\n",
    "available_tickets_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Relationships: \n",
    "* the target is the leaf (or the customer)\n",
    "* the source is the branch (or the ticket)\n",
    "\n",
    "you can add extra stuff to the relationship. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbe\n",
      "customer-cc04f3b6-39b0-4cef-bfff-a7d668cce446\n"
     ]
    }
   ],
   "source": [
    "print(customer_selection)\n",
    "print(user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'$relationshipId': 'ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbeownedBycustomer-cc04f3b6-39b0-4cef-bfff-a7d668cce446',\n",
       " '$etag': 'W/\"63140a7e-b8e9-4638-909e-9adf67a05d0d\"',\n",
       " '$sourceId': 'ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbe',\n",
       " '$relationshipName': 'ownedBy',\n",
       " '$targetId': 'customer-cc04f3b6-39b0-4cef-bfff-a7d668cce446',\n",
       " 'bought_online': True}"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tickethoder_relationship = {\n",
    "        \"$relationshipId\": f\"{customer_selection}ownedBy{user}\",\n",
    "        \"$sourceId\": customer_selection,\n",
    "        \"$relationshipName\": \"ownedBy\",\n",
    "        \"$targetId\": user,\n",
    "        \"bought_online\": True\n",
    "    }\n",
    "\n",
    "service_client.upsert_relationship(\n",
    "        tickethoder_relationship[\"$sourceId\"],\n",
    "        tickethoder_relationship[\"$relationshipId\"],\n",
    "        tickethoder_relationship\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[i for i in service_client.list_relationships(customer_selection)]\n",
    "i"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another customer will buy several tickets. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>$dtId</th>\n",
       "      <th>event_title</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ticket-52e25a0a-06b1-428f-b6a2-479cafc45c16</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ticket-58bf00bf-d5b8-4d06-9002-f8bf1693cc56</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ticket-67f3cbfb-4b35-4e10-bf6d-3fd216093c3c</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ticket-3f82286d-7e0b-4df5-a491-d9e28f7c94f9</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ticket-cf70a216-0183-4458-8e13-94f432f7b8c0</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ticket-260e3349-adc5-41af-9ae5-4f3690d813c7</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ticket-9f2bc5f4-b7d6-4f86-a1f8-c570cb263717</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ticket-b7a550e8-0fa8-46ff-8b4c-1a290a5d706c</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ticket-57a45e6f-f42a-41b0-a0f5-68e5e0fd67ba</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ticket-7f414b3f-fde6-4cbc-ad58-86c7ccfb1109</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ticket-2d69b397-3ec3-4588-9eaf-5eb931f1f8c4</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>ticket-e9fc6ad3-d7ad-4ef1-8a58-0759a0f27a00</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ticket-dc48bc69-b1f5-4850-b5af-037c4ea72961</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ticket-92826a40-b024-4b91-a4d2-7e20dcfddce3</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>ticket-8c483b5c-37d4-4027-8d67-d920bf8ca063</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>ticket-09028447-8e23-4e4f-a100-098a4dc8e919</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ticket-ccc89397-0d52-42a3-8fe1-39c97158c87c</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          $dtId        event_title state\n",
       "0   ticket-52e25a0a-06b1-428f-b6a2-479cafc45c16  Smashing Pumpkins  open\n",
       "1   ticket-58bf00bf-d5b8-4d06-9002-f8bf1693cc56       Foo Fighters  open\n",
       "2   ticket-67f3cbfb-4b35-4e10-bf6d-3fd216093c3c       Foo Fighters  open\n",
       "3   ticket-3f82286d-7e0b-4df5-a491-d9e28f7c94f9       Foo Fighters  open\n",
       "4   ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9            Nirvana  open\n",
       "5   ticket-cf70a216-0183-4458-8e13-94f432f7b8c0            Nirvana  open\n",
       "6   ticket-260e3349-adc5-41af-9ae5-4f3690d813c7            Nirvana  open\n",
       "7   ticket-9f2bc5f4-b7d6-4f86-a1f8-c570cb263717            Nirvana  open\n",
       "8   ticket-b7a550e8-0fa8-46ff-8b4c-1a290a5d706c       Foo Fighters  open\n",
       "9   ticket-57a45e6f-f42a-41b0-a0f5-68e5e0fd67ba  Smashing Pumpkins  open\n",
       "10  ticket-7f414b3f-fde6-4cbc-ad58-86c7ccfb1109  Smashing Pumpkins  open\n",
       "11  ticket-2d69b397-3ec3-4588-9eaf-5eb931f1f8c4  Smashing Pumpkins  open\n",
       "12  ticket-e9fc6ad3-d7ad-4ef1-8a58-0759a0f27a00  Smashing Pumpkins  open\n",
       "13  ticket-dc48bc69-b1f5-4850-b5af-037c4ea72961  Smashing Pumpkins  open\n",
       "14  ticket-92826a40-b024-4b91-a4d2-7e20dcfddce3       Foo Fighters  open\n",
       "15  ticket-8c483b5c-37d4-4027-8d67-d920bf8ca063       Foo Fighters  open\n",
       "16  ticket-09028447-8e23-4e4f-a100-098a4dc8e919       Foo Fighters  open\n",
       "17  ticket-ccc89397-0d52-42a3-8fe1-39c97158c87c       Foo Fighters  open"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_expression = f\"\"\"\n",
    "SELECT * FROM digitaltwins t where IS_OF_MODEL('{ticket_model_id}') \n",
    "and t.state = 'open'\n",
    "\"\"\"\n",
    "query_result = service_client.query_twins(query_expression)\n",
    "available_tickets_df = pd.DataFrame([[i['$dtId'],i['event_title'],i['state']] for i in query_result],\n",
    "                                   columns = ['$dtId','event_title','state'])\n",
    "available_tickets_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'customer-25e19268-3433-4f09-afe3-94f466313368'"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "other_user = df_customers.loc[len(df_customers)-1]['id']\n",
    "other_user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ticket-52e25a0a-06b1-428f-b6a2-479cafc45c16',\n",
       " 'ticket-58bf00bf-d5b8-4d06-9002-f8bf1693cc56',\n",
       " 'ticket-67f3cbfb-4b35-4e10-bf6d-3fd216093c3c',\n",
       " 'ticket-3f82286d-7e0b-4df5-a491-d9e28f7c94f9',\n",
       " 'ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9',\n",
       " 'ticket-cf70a216-0183-4458-8e13-94f432f7b8c0']"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tickets_bought = available_tickets_df.loc[:5,'$dtId'].tolist()\n",
    "tickets_bought"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ticket-52e25a0a-06b1-428f-b6a2-479cafc45c16',\n",
       " 'ticket-58bf00bf-d5b8-4d06-9002-f8bf1693cc56',\n",
       " 'ticket-67f3cbfb-4b35-4e10-bf6d-3fd216093c3c',\n",
       " 'ticket-3f82286d-7e0b-4df5-a491-d9e28f7c94f9',\n",
       " 'ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9',\n",
       " 'ticket-cf70a216-0183-4458-8e13-94f432f7b8c0']"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tickets_bought"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ticket-52e25a0a-06b1-428f-b6a2-479cafc45c16\n",
      "ticket-58bf00bf-d5b8-4d06-9002-f8bf1693cc56\n",
      "ticket-67f3cbfb-4b35-4e10-bf6d-3fd216093c3c\n",
      "ticket-3f82286d-7e0b-4df5-a491-d9e28f7c94f9\n",
      "ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9\n",
      "ticket-cf70a216-0183-4458-8e13-94f432f7b8c0\n"
     ]
    }
   ],
   "source": [
    "for t in tickets_bought:\n",
    "    print(t)\n",
    "    tickethoder_relationship = {\n",
    "            \"$relationshipId\": f\"{customer_selection}ownedBy{other_user}\",\n",
    "            \"$sourceId\": t,\n",
    "            \"$relationshipName\": \"ownedBy\",\n",
    "            \"$targetId\": other_user,\n",
    "            \"bought_online\": False\n",
    "        }\n",
    "\n",
    "    service_client.upsert_relationship(\n",
    "            tickethoder_relationship[\"$sourceId\"],\n",
    "            tickethoder_relationship[\"$relationshipId\"],\n",
    "            tickethoder_relationship\n",
    "        )\n",
    "    customer_selection = available_tickets_df.loc[0]['$dtId']\n",
    "\n",
    "    patch = [\n",
    "        {\n",
    "            \"op\": \"replace\",\n",
    "            \"path\": \"\",\n",
    "            \"value\": \"sold\"\n",
    "        }\n",
    "    ]\n",
    "    service_client.update_component(t,\"state\", patch)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's lets look at the tickets. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>$dtId</th>\n",
       "      <th>event_title</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ticket-600f2149-1ed8-4cb4-b0b8-bb6d03f575cd</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ticket-b8860b9b-cc54-4591-abb3-2abc3adc0800</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ticket-2256b8c9-1dd8-4ef8-a00a-b191dc122532</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ticket-18663c82-67a2-4791-b1c4-05db261af867</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ticket-99023c6e-5d97-4e43-ab08-3d893233c8b0</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ticket-132839a8-a4a3-4c75-ae96-9ff4085a07d4</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ticket-79f3f382-bd65-4788-8ad1-0db507b8a3ab</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ticket-9f7c0906-9729-4568-a559-89d63b634d09</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ticket-633f4537-50a1-4735-9e70-99bf19be51e4</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ticket-52e25a0a-06b1-428f-b6a2-479cafc45c16</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ticket-ca767da7-0152-4861-9be6-fbe39b5dd9d8</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ticket-0cd531c6-d81c-4c4c-84c9-9592a8f43e70</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>ticket-58bf00bf-d5b8-4d06-9002-f8bf1693cc56</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ticket-67f3cbfb-4b35-4e10-bf6d-3fd216093c3c</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ticket-3f82286d-7e0b-4df5-a491-d9e28f7c94f9</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbe</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ticket-cf70a216-0183-4458-8e13-94f432f7b8c0</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>sold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>ticket-260e3349-adc5-41af-9ae5-4f3690d813c7</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>ticket-9f2bc5f4-b7d6-4f86-a1f8-c570cb263717</td>\n",
       "      <td>Nirvana</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>ticket-b7a550e8-0fa8-46ff-8b4c-1a290a5d706c</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>ticket-57a45e6f-f42a-41b0-a0f5-68e5e0fd67ba</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>ticket-7f414b3f-fde6-4cbc-ad58-86c7ccfb1109</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>ticket-2d69b397-3ec3-4588-9eaf-5eb931f1f8c4</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>ticket-e9fc6ad3-d7ad-4ef1-8a58-0759a0f27a00</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>ticket-dc48bc69-b1f5-4850-b5af-037c4ea72961</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>ticket-92826a40-b024-4b91-a4d2-7e20dcfddce3</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>ticket-8c483b5c-37d4-4027-8d67-d920bf8ca063</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>ticket-09028447-8e23-4e4f-a100-098a4dc8e919</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>ticket-ccc89397-0d52-42a3-8fe1-39c97158c87c</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          $dtId        event_title state\n",
       "0   ticket-600f2149-1ed8-4cb4-b0b8-bb6d03f575cd            Nirvana  sold\n",
       "1   ticket-b8860b9b-cc54-4591-abb3-2abc3adc0800            Nirvana  sold\n",
       "2   ticket-2256b8c9-1dd8-4ef8-a00a-b191dc122532            Nirvana  sold\n",
       "3   ticket-18663c82-67a2-4791-b1c4-05db261af867  Smashing Pumpkins  sold\n",
       "4   ticket-99023c6e-5d97-4e43-ab08-3d893233c8b0            Nirvana  sold\n",
       "5   ticket-132839a8-a4a3-4c75-ae96-9ff4085a07d4            Nirvana  sold\n",
       "6   ticket-79f3f382-bd65-4788-8ad1-0db507b8a3ab  Smashing Pumpkins  sold\n",
       "7   ticket-9f7c0906-9729-4568-a559-89d63b634d09  Smashing Pumpkins  sold\n",
       "8   ticket-633f4537-50a1-4735-9e70-99bf19be51e4  Smashing Pumpkins  sold\n",
       "9   ticket-52e25a0a-06b1-428f-b6a2-479cafc45c16  Smashing Pumpkins  sold\n",
       "10  ticket-ca767da7-0152-4861-9be6-fbe39b5dd9d8       Foo Fighters  sold\n",
       "11  ticket-0cd531c6-d81c-4c4c-84c9-9592a8f43e70       Foo Fighters  sold\n",
       "12  ticket-58bf00bf-d5b8-4d06-9002-f8bf1693cc56       Foo Fighters  sold\n",
       "13  ticket-67f3cbfb-4b35-4e10-bf6d-3fd216093c3c       Foo Fighters  sold\n",
       "14  ticket-3f82286d-7e0b-4df5-a491-d9e28f7c94f9       Foo Fighters  sold\n",
       "15  ticket-b10211a2-1a7a-4f6f-9fc3-d23daefe8cbe            Nirvana  sold\n",
       "16  ticket-d1d2ad08-add8-4f5f-97cf-0b6516914cc9            Nirvana  sold\n",
       "17  ticket-cf70a216-0183-4458-8e13-94f432f7b8c0            Nirvana  sold\n",
       "18  ticket-260e3349-adc5-41af-9ae5-4f3690d813c7            Nirvana  open\n",
       "19  ticket-9f2bc5f4-b7d6-4f86-a1f8-c570cb263717            Nirvana  open\n",
       "20  ticket-b7a550e8-0fa8-46ff-8b4c-1a290a5d706c       Foo Fighters  open\n",
       "21  ticket-57a45e6f-f42a-41b0-a0f5-68e5e0fd67ba  Smashing Pumpkins  open\n",
       "22  ticket-7f414b3f-fde6-4cbc-ad58-86c7ccfb1109  Smashing Pumpkins  open\n",
       "23  ticket-2d69b397-3ec3-4588-9eaf-5eb931f1f8c4  Smashing Pumpkins  open\n",
       "24  ticket-e9fc6ad3-d7ad-4ef1-8a58-0759a0f27a00  Smashing Pumpkins  open\n",
       "25  ticket-dc48bc69-b1f5-4850-b5af-037c4ea72961  Smashing Pumpkins  open\n",
       "26  ticket-92826a40-b024-4b91-a4d2-7e20dcfddce3       Foo Fighters  open\n",
       "27  ticket-8c483b5c-37d4-4027-8d67-d920bf8ca063       Foo Fighters  open\n",
       "28  ticket-09028447-8e23-4e4f-a100-098a4dc8e919       Foo Fighters  open\n",
       "29  ticket-ccc89397-0d52-42a3-8fe1-39c97158c87c       Foo Fighters  open"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_expression = f\"\"\"\n",
    "SELECT * FROM digitaltwins t where IS_OF_MODEL('{ticket_model_id}') \n",
    "\"\"\"\n",
    "query_result = service_client.query_twins(query_expression)\n",
    "available_tickets_df = pd.DataFrame([[i['$dtId'],i['event_title'],i['state']] for i in query_result],\n",
    "                                   columns = ['$dtId','event_title','state'])\n",
    "available_tickets_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can see that our web app can easily filter on tickets that are open and give a person specific rows. We will get into more complicated analysis in the next steps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (azure_test)",
   "language": "python",
   "name": "azure_test"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}